﻿/*
 * 2010/05/31 By Todd：QueryUserDevRight()中，SQL中最後的Group條件要加入City，才不會有Error
 * 2010/06/01 By Lights: QuerySingleSiteData(string SiteID)中，可允許只查詢單一WebSite(通常使用在從iEN-Adv單一登入的使用者)
 * 2010/06/02 By May：新增 GetTagBySiteID_GrpNo，根據案場、案場下之設備資料產生監控點資料
 * 2010/06/22 By Victor：修改QueryUserRight，顯示UserName
 * 2010/08/18 By gtkuo：修改QueryUserDevRight()SQL語法過濾案場設備僅秀出彙整點條件為GroupConfig_cfg.SumFlag = '1'
 * 2010/08/19 By gtkuo：修改QueryUserDevRight()SQL語法增加查詢出StatusFlag欄位
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

//Using Namespace
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    /// <summary>
    /// 太陽能專案通用函示
    /// </summary>
    public class BasicFun
    {
        private string strSCADADSN = System.Configuration.ConfigurationManager.AppSettings["strDSN"];
       
        string strSQL;



        //功能說明：選單顯示用函示      相關Table：Web_Site    
        //Edit  By Victor
        #region public DataTable QueryAllSite()      //查詢所有案場資料
        /// <summary>
        /// 查詢所有案場資料
        /// </summary>
        /// <returns>回傳相關資料</returns>
        public DataTable QueryAllSite()
        {
            try
            {
                strSQL = "Select * From Web_Site Order by UpdateTime Desc";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryAllSite", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QuerySingleSite(string SiteID)         By Light
        /// <summary>
        /// 查詢單一案場資料，大部分For Advanced使用
        /// </summary>
        /// <param name="SiteID">案場ID</param>
        /// <returns>回傳案場相關資料</returns>
        public DataTable QuerySingleSite(string SiteID)
        {
            try
            {
                strSQL = "Select * From Web_Site WHERE SiteID='" + SiteID + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QuerySingleSite", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion
 
        #region public DataTable QuerySiteByRight()      //根據權限查詢所有案場資料
        /// <summary>
        /// 查詢所有案場資料
        /// </summary>
        /// <returns>回傳相關資料</returns>
        public DataTable QuerySiteByRight()
        {
            try
            {
                strSQL = "Select * From Web_Site Join  Web_UserRight On Web_Site.SiteID = Web_UserRight.SiteID Order by Web_Site.UpdateTime Desc";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QuerySiteByRight", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable GetSite()      //產生已設定Config之站別資料
        /// <summary>
        /// 產生站別資料（已設定Config之案場資料)
        /// </summary>
        /// <returns>回傳站台相關資料</returns>
        public DataTable GetSite()
        {
            try
            {
                strSQL = "Select distinct Web_Site.* From Web_Site Join GroupConfig_cfg On Web_Site.SiteID = GroupConfig_cfg.SiteID";
                return new CommonFun ().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun ().AddErrorMsg("GetSite", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable GetDeviceBySiteID(string SiteID)       //根據站別資料產生該站台下之設備資料
        /// <summary>
        /// 根據站別資料產生該站台下之設備資料
        /// </summary>
        /// <param name="SiteID">站別ID</param>
        /// <returns>該站台下之設備資料</returns>
        public DataTable GetDeviceBySiteID(string SiteID)
        {
            try
            {
                strSQL = "Select * From GroupConfig_cfg ";
                strSQL += " Where SiteID = '" + SiteID + "' and bytIsDownLevel='0'";
                return new CommonFun ().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("GetDeviceBySiteID", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region  public DataTable GetTagBySiteID_GrpNo(string SiteID, string GrpNo)//根據案場、案場下之設備資料產生監控點資料
        /// <summary>
        /// 根據案場、案場下之設備資料產生監控點資料
        /// </summary>
        /// <param name="SiteID">案場</param>
        /// <param name="GrpNo">設備</param>
        /// <returns>根據案場、案場下之設備資料產生監控點資料</returns>
        public DataTable GetTagBySiteID_GrpNo(string SiteID, string GrpNo)
        {
            try
            {
                strSQL = "Select * From TagConfig_cfg ";
                strSQL += " Where SiteID = '" + SiteID + "' and GrpNo='" + GrpNo + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("GetTagBySiteID_GrpNo", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion



        //功能說明：依照Config設定查詢使用權限（有設定Config檔之案場才會顯示）
        #region public DataTable QueryConfigRight-1     //查詢使用者有設定Config之案場權限資料
        /// <summary>
        /// 查詢使用者有設定Config之案場權限資料
        /// </summary>
        /// <param name="UserID">使用者帳號</param>
        /// <returns>回傳該權限能查詢之案場資料</returns>
        public DataTable QueryConfigRight(string UserID)
        {
            try
            {
                strSQL = "Select distinct Web_Site.*, Web_UserRight.UserID, Web_UserRight.Role  From Web_Site Join GroupConfig_cfg On Web_Site.SiteID = GroupConfig_cfg.SiteID Join Web_UserRight On Web_Site.SiteID = Web_UserRight.SiteID ";
                strSQL += " Where Web_UserRight.UserID='" + UserID + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryConfigRight", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryConfigRight-2     //查詢使用者有設定Config之案場權限資料
        /// <summary>
        /// 查詢使用者有設定Config之案場權限資料
        /// </summary>
        /// <param name="UserID">使用者帳號</param>
        /// <param name="SiteID">案場代號</param>
        /// <returns>回傳該權限能查詢之案場資料</returns>
        public DataTable QueryConfigRight(string UserID, string SiteID)
        {
            try
            {
                strSQL = "Select distinct Web_Site.*, Web_UserRight.UserID, Web_UserRight.Role  From Web_Site Join GroupConfig_cfg On Web_Site.SiteID = GroupConfig_cfg.SiteID Join Web_UserRight On Web_Site.SiteID = Web_UserRight.SiteID ";
                strSQL += " Where Web_UserRight.UserID='" + UserID + "' and Web_UserRight.SiteID = '" + SiteID + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryConfigRight-2", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryConfigRight-3     //查詢使用者有設定Config之案場權限資料
        /// <summary>
        /// 查詢使用者有設定Config之案場權限資料
        /// </summary>
        /// <param name="UserID">使用者帳號</param>
        /// <param name="Role">身份（1：案場管理者、2：一般使用者）</param>
        /// <returns>回傳該權限能查詢之案場資料</returns>
        public DataTable QueryConfigRight(string UserID, int Role)
        {
            try
            {
                string strRole = Role.ToString();
                strSQL = "Select distinct Web_Site.*, Web_UserRight.UserID, Web_UserRight.Role  From Web_Site Join GroupConfig_cfg On Web_Site.SiteID = GroupConfig_cfg.SiteID Join Web_UserRight On Web_Site.SiteID = Web_UserRight.SiteID ";
                strSQL += " Where Web_UserRight.UserID='" + UserID + "' and Web_UserRight.Role = '" + strRole + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryConfigRight-3", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryConfigRight-4     //查詢使用者有設定Config之案場權限資料
        /// <summary>
        /// 查詢使用者有設定Config之案場權限資料
        /// </summary>
        /// <param name="UserID">使用者帳號</param>
        /// <param name="SiteID">案場代號</param>
        /// <param name="Role">身份</param>
        /// <returns>回傳該權限能查詢之案場資料</returns>
        public DataTable QueryConfigRight(string UserID, string SiteID, string Role)
        {
            try
            {
                strSQL = "Select distinct Web_Site.*, Web_UserRight.UserID, Web_UserRight.Role  From Web_Site Join GroupConfig_cfg On Web_Site.SiteID = GroupConfig_cfg.SiteID Join Web_UserRight On Web_Site.SiteID = Web_UserRight.SiteID ";
                strSQL += " Where Web_UserRight.UserID='" + UserID + "' and Web_UserRight.SiteID = '" + SiteID + "' and Web_UserRight.Role ='" + Role + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryConfigRight-4", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion


        //功能說明：查詢使用者權限      相關Table：Web_UserRight
        #region public DataTable QueryUserRight-1     //查詢使用者權限設定資料
        /// <summary>
        /// 查詢使用者權限設定資料
        /// </summary>
        /// <param name="UserID">使用者帳號</param>
        /// <returns>回傳該權限能查詢之案場資料</returns>
        public DataTable QueryUserRight(string UserID)
        {
            try
            {
                strSQL = "Select Distinct Web_UserRight.* ,Web_Site.*, WebUser.UserName, Case Web_UserRight.Role When '1' Then '案場管理者' When '2' Then '一般使用者' End as ShowRoleName From Web_Site Join Web_UserRight On Web_Site.SiteID = Web_UserRight.SiteID Join WebUser On Web_UserRight.UserID = WebUser.UserID";
                strSQL += " Where Web_UserRight.UserID='" + UserID + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryUserRight", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryUserRight-2     //查詢使用者權限設定資料
        /// <summary>
        /// 查詢使用者權限設定資料
        /// </summary>
        /// <param name="UserID">使用者帳號</param>
        /// <param name="SiteID">案場代號</param>
        /// <returns>回傳該權限能查詢之案場資料</returns>
        public DataTable QueryUserRight(string UserID, string SiteID)
        {
            try
            {
                strSQL = "Select Distinct Web_UserRight.* ,Web_Site.*, WebUser.UserName, Case Web_UserRight.Role When '1' Then '案場管理者' When '2' Then '一般使用者' End as ShowRoleName From Web_Site Join Web_UserRight On Web_Site.SiteID = Web_UserRight.SiteID Join WebUser On Web_UserRight.UserID = WebUser.UserID";
                strSQL += " Where Web_UserRight.UserID='" + UserID + "' and Web_UserRight.SiteID = '" + SiteID + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryUserRight", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryUserRight-3     //查詢使用者權限設定資料
        /// <summary>
        /// 查詢使用者權限設定資料
        /// </summary>
        /// <param name="UserID">使用者帳號</param>
        /// <param name="Role">身份（1：案場管理者、2：一般使用者）</param>
        /// <returns>回傳該權限能查詢之案場資料</returns>
        public DataTable QueryUserRight(string UserID, int Role)
        {
            try
            {
                string strRole = Role.ToString();
                strSQL = "Select Distinct Web_UserRight.* ,Web_Site.*, WebUser.UserName, Case Web_UserRight.Role When '1' Then '案場管理者' When '2' Then '一般使用者' End as ShowRoleName From Web_Site Join Web_UserRight On Web_Site.SiteID = Web_UserRight.SiteID Join WebUser On Web_UserRight.UserID = WebUser.UserID";
                strSQL += " Where Web_UserRight.UserID='" + UserID + "' and Web_UserRight.Role = '" + strRole + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryUserRight", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryUserRight-4     //查詢使用者權限設定資料
        /// <summary>
        /// 查詢使用者權限設定資料
        /// </summary>
        /// <param name="UserID">使用者帳號</param>
        /// <param name="SiteID">案場代號</param>
        /// <param name="Role">身份</param>
        /// <returns>回傳該權限能查詢之案場資料</returns>
        public DataTable QueryUserRight(string UserID, string SiteID, string Role)
        {
            try
            {
                strSQL = "Select Distinct Web_UserRight.* ,Web_Site.*, WebUser.UserName, Case Web_UserRight.Role When '1' Then '案場管理者' When '2' Then '一般使用者' End as ShowRoleName From Web_Site Join Web_UserRight On Web_Site.SiteID = Web_UserRight.SiteID Join WebUser On Web_UserRight.UserID = WebUser.UserID";
                strSQL += " Where Web_UserRight.UserID='" + UserID + "' and Web_UserRight.SiteID = '" + SiteID + "' and Web_UserRight.Role ='" + Role + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryUserRight", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion



        //功能說明：查詢使用者權限對應相關案場設備資料    相關Table：GroupConfig_cfg、Web_UserRight   For：RealTime.aspx
        #region public DataTable QueryUserDevRight      //查詢使用者權限對應相關案場設備資料
        /// <summary>
        /// 查詢使用者權限對應相關案場設備資料
        /// </summary>
        /// <param name="UserID">使用者ID</param>
        /// <returns>回傳使用者權限對應相關案場設備資料</returns>
        public DataTable QueryUserDevRight(string UserID)
        {
            try
            {
                if (UserID != "All")
                {
                    strSQL = "Select GrpNo, GrpName, GroupConfig_cfg.SiteID, SiteName, City, Web_UserRight.UserID, Web_UserRight.Role From GroupConfig_cfg ";
                    strSQL += " Join Web_Site On GroupConfig_cfg.SiteID = Web_Site.SiteID ";
                    strSQL += " Join Web_UserRight On GroupConfig_cfg.SiteID = Web_UserRight.SiteID ";
                    strSQL += " Where GroupConfig_cfg.LV ='6'  and Web_UserRight.UserID='" + UserID + "'";
                    strSQL += " Group by GrpNo, GrpName,GroupConfig_cfg.SiteID, SiteName, Web_UserRight.UserID, Web_UserRight.Role, City";
                    return new CommonFun().GenDataTable(strSQL);
                }
                else
                {
                    strSQL = "Select distinct GrpNo, GrpName, GroupConfig_cfg.SiteID, SiteName, City From GroupConfig_cfg ";
                    strSQL += " Join Web_Site On GroupConfig_cfg.SiteID = Web_Site.SiteID ";
                    strSQL += " Where GroupConfig_cfg.LV ='6'";
                    return new CommonFun().GenDataTable(strSQL);
                }
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryUserDevRight", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion



        #region public DataTable QueryKeyDefinition     //查詢KeyDefinition
        /// <summary>
        /// 查詢資料庫中KeyDefinition的資料
        /// </summary>
        /// <param name="NameSpace">資料庫中NameSpace欄位</param>
        /// <returns>回傳該NameSpace之Definition資料</returns>
        public DataTable QueryKeyDefinition(string NameSpace)
        {
            try
            {
                strSQL = "SELECT NameSpace, Item, ItemValue FROM Web_KeyDefinitionConfig WHERE (NameSpace LIKE '" + NameSpace + "%') ORDER BY Sequence, NameSpace ";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryKeyDefinition", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion


    }
}
